package com.zhilei;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class ExcelToWellDynagraphs {
    public static void main(String[] args) {

        // TODO 这是数据库配置的基本信息
        String excelFilePath = "C:\\Users\\16833\\Desktop\\实时数据服务接口列表.xls";
        String jdbcURL = "jdbc:mysql://localhost/ruoyi?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        String username = "root";
        String password = "root";
        String targetTable = "well_dynagraphs";

        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = getWorkbook(fis, excelFilePath);
             Connection conn = DriverManager.getConnection(jdbcURL, username, password)) {

            // TODO 关闭自动提交，提高性能
            conn.setAutoCommit(false);

            // TODO 创建插入的sql
            String sql = "INSERT INTO " + targetTable +
                    " (param_code, param_name, param_type, param_length, param_scale, param_unit, " +
                    "is_required, create_user, create_time, update_user, update_time, remark, type) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                // TODO 格式化时间、日期格式
                DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

                // TODO 循环判断当前的sheet编号
                for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                    Sheet sheet = workbook.getSheetAt(sheetIndex);
                    // Sheet 1 -> type=1, Sheet 2 -> type=2，依次递增
                    int typeValue = sheetIndex + 1;

                    // TODO 获取excel的第5行
                    Row headerRow = sheet.getRow(4);

                    // TODO 如果第5行是空的，跳出本次循环
                    if (headerRow == null) continue;

                    for (int rowIndex = 5; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                        Row row = sheet.getRow(rowIndex);
                        if (row == null) continue;

                        // TODO 读取从 **第3列（索引2）** 开始的数据
                        pstmt.setString(1, getCellValue(row, 2)); // param_code
                        pstmt.setString(2, getCellValue(row, 3)); // param_name
                        pstmt.setString(3, getCellValue(row, 4)); // param_type
                        pstmt.setInt(4, parseInteger(getCellValue(row, 5))); // param_length
                        pstmt.setInt(5, parseInteger(getCellValue(row, 6))); // param_scale
                        pstmt.setString(6, getCellValue(row, 7)); // param_unit
                        pstmt.setInt(7, parseInteger(getCellValue(row, 8))); // is_required
                        pstmt.setString(8, "system");
                        pstmt.setString(9, dtf.format(LocalDateTime.now()));
                        pstmt.setString(10, "system");
                        pstmt.setString(11, dtf.format(LocalDateTime.now()));
                        pstmt.setString(12, "");
                        pstmt.setInt(13, typeValue);

                        pstmt.addBatch();
                    }
                }
                pstmt.executeBatch();
            }

            conn.commit();
            System.out.println("Excel 数据成功导入到 " + targetTable);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static int parseInteger(String value) {
        try {
            return value.isEmpty() ? 0 : Integer.parseInt(value);
        } catch (NumberFormatException e) {
            return 0;
        }
    }

    private static String getCellValue(Row row, int columnIndex) {
        Cell cell = row.getCell(columnIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        // TODO 去掉前后的空格，转换成字符串
        return cell.toString().trim();
    }

    // **自动判断 Excel 版本**
    private static Workbook getWorkbook(FileInputStream fis, String filePath) throws IOException {
        if (filePath.toLowerCase().endsWith(".xls")) {
            // 处理 Excel 97-2003（.xls）
            return new HSSFWorkbook(fis);
        } else if (filePath.toLowerCase().endsWith(".xlsx")) {
            // 处理 Excel 2007+（.xlsx）
            return new XSSFWorkbook(fis);
        } else {
            throw new IllegalArgumentException("文件格式不支持: " + filePath);
        }
    }
}
